*** E5 Fuel ***

** oil 
putexcel set "$P_Data_Processed/structural_breaks_oil_e5.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_oil/Separate" files "*e5.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_oil/Separate/`f'", clear
    quietly capture tsset y2kW
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg nRes nShock nPC
    quietly capture estat sbsingle, breakvars(nShock)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** weather
putexcel set "$P_Data_Processed/structural_breaks_weather_e5.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_weather/Separate" files "*e5.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_weather/Separate/`f'", clear
    quietly capture tsset y2kW
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg nRes nShock nPC
    quietly capture estat sbsingle, breakvars(nShock)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** for pccount and pcresponse, control for weekly oil prices: 
clear
** get oil prices
insheet using "$P_Data/Daily_Crude_Oil/Crude_Oil.csv"
gener date2 = date(date,"YMD")
drop date
rename date2 date
* oil price per litre
replace oil = oil/159
save "$P_Data_Processed/oil_prices.dta", replace
* grab exchange rates to convert to EUR from USD
clear
insheet using "$P_Data/Daily_Crude_Oil/EUR_USD.csv", names
gener date2 = date(date,"YMD")
drop date
rename date2 date
merge 1:1 date using "$P_Data_Processed/oil_prices.dta"
keep if _merge==3
drop _merge
destring eur_usd, replace force
gener oil_eur = oil/eur_usd
keep oil_eur date
save "$P_Data_Processed/oil_prices.dta", replace
gener weekly_date = yw(year(date), week(date))
drop if oil_eur==.
collapse oil_eur, by(weekly_date)

merge 1:1 weekly_date using "$P_Data_Processed/week_y2kW_concordance.dta"
keep if _merge==3
drop _merge
keep oil_eur y2kW
save "$P_Data_Processed/weekly_oil.dta" , replace

clear 

** pccount
putexcel set "$P_Data_Processed/structural_breaks_pccount_e5.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_pccount/Separate" files "*e5.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_pccount/Separate/`f'", clear
    quietly capture tsset y2kW
	quietly merge 1:1 y2kW using "$P_Data_Processed/weekly_oil.dta"
	quietly capture keep if _merge==3 
	quietly capture drop _merge
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg PCCount oil_eur
    quietly capture estat sbsingle, breakvars(, const)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** pcresponse 
putexcel set "$P_Data_Processed/structural_breaks_pcresponse_e5.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_pcresponse/Separate" files "*e5.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_pcresponse/Separate/`f'", clear
    quietly capture tsset y2kW
	quietly merge 1:1 y2kW using "$P_Data_Processed/weekly_oil.dta"
	quietly capture keep if _merge==3 
	quietly capture drop _merge
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg Response PCCount oil_eur
    quietly capture estat sbsingle, breakvars(, const)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

*** E10 Fuel ***

** oil 
putexcel set "$P_Data_Processed/structural_breaks_oil_e10.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_oil/Separate" files "*e10.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_oil/Separate/`f'", clear
    quietly capture tsset y2kW
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg nRes nShock nPC
    quietly capture estat sbsingle, breakvars(nShock)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** weather
putexcel set "$P_Data_Processed/structural_breaks_weather_e10.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_weather/Separate" files "*e10.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_weather/Separate/`f'", clear
    quietly capture tsset y2kW
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg nRes nShock nPC
    quietly capture estat sbsingle, breakvars(nShock)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** for pccount and pcresponse, control for weekly oil prices: 
clear
** get oil prices
insheet using "$P_Data/Daily_Crude_Oil/Crude_Oil.csv"
gener date2 = date(date,"YMD")
drop date
rename date2 date
* oil price per litre
replace oil = oil/159
save "$P_Data_Processed/oil_prices.dta", replace
* grab exchange rates to convert to EUR from USD
clear
insheet using "$P_Data/Daily_Crude_Oil/EUR_USD.csv", names
gener date2 = date(date,"YMD")
drop date
rename date2 date
merge 1:1 date using "$P_Data_Processed/oil_prices.dta"
keep if _merge==3
drop _merge
destring eur_usd, replace force
gener oil_eur = oil/eur_usd
keep oil_eur date
save "$P_Data_Processed/oil_prices.dta", replace
gener weekly_date = yw(year(date), week(date))
drop if oil_eur==.
collapse oil_eur, by(weekly_date)

merge 1:1 weekly_date using "$P_Data_Processed/week_y2kW_concordance.dta"
keep if _merge==3
drop _merge
keep oil_eur y2kW
save "$P_Data_Processed/weekly_oil.dta" , replace

clear 

** pccount
putexcel set "$P_Data_Processed/structural_breaks_pccount_e10.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_pccount/Separate" files "*e10.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_pccount/Separate/`f'", clear
    quietly capture tsset y2kW
	quietly merge 1:1 y2kW using "$P_Data_Processed/weekly_oil.dta"
	quietly capture keep if _merge==3 
	quietly capture drop _merge
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg PCCount oil_eur
    quietly capture estat sbsingle, breakvars(, const)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** pcresponse 
putexcel set "$P_Data_Processed/structural_breaks_pcresponse_e10.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_pcresponse/Separate" files "*e10.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_pcresponse/Separate/`f'", clear
    quietly capture tsset y2kW
	quietly merge 1:1 y2kW using "$P_Data_Processed/weekly_oil.dta"
	quietly capture keep if _merge==3 
	quietly capture drop _merge
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg Response PCCount oil_eur
    quietly capture estat sbsingle, breakvars(, const)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}


*** EDiesel Fuel ***

** oil 
putexcel set "$P_Data_Processed/structural_breaks_oil_diesel.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_oil/Separate" files "*diesel.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_oil/Separate/`f'", clear
    quietly capture tsset y2kW
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg nRes nShock nPC
    quietly capture estat sbsingle, breakvars(nShock)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** weather
putexcel set "$P_Data_Processed/structural_breaks_weather_diesel.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_weather/Separate" files "*diesel.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_weather/Separate/`f'", clear
    quietly capture tsset y2kW
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg nRes nShock nPC
    quietly capture estat sbsingle, breakvars(nShock)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** for pccount and pcresponse, control for weekly oil prices: 
clear
** get oil prices
insheet using "$P_Data/Daily_Crude_Oil/Crude_Oil.csv"
gener date2 = date(date,"YMD")
drop date
rename date2 date
* oil price per litre
replace oil = oil/159
save "$P_Data_Processed/oil_prices.dta", replace
* grab exchange rates to convert to EUR from USD
clear
insheet using "$P_Data/Daily_Crude_Oil/EUR_USD.csv", names
gener date2 = date(date,"YMD")
drop date
rename date2 date
merge 1:1 date using "$P_Data_Processed/oil_prices.dta"
keep if _merge==3
drop _merge
destring eur_usd, replace force
gener oil_eur = oil/eur_usd
keep oil_eur date
save "$P_Data_Processed/oil_prices.dta", replace
gener weekly_date = yw(year(date), week(date))
drop if oil_eur==.
collapse oil_eur, by(weekly_date)

merge 1:1 weekly_date using "$P_Data_Processed/week_y2kW_concordance.dta"
keep if _merge==3
drop _merge
keep oil_eur y2kW
save "$P_Data_Processed/weekly_oil.dta" , replace

clear 

** pccount
putexcel set "$P_Data_Processed/structural_breaks_pccount_diesel.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_pccount/Separate" files "*diesel.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_pccount/Separate/`f'", clear
    quietly capture tsset y2kW
	quietly merge 1:1 y2kW using "$P_Data_Processed/weekly_oil.dta"
	quietly capture keep if _merge==3 
	quietly capture drop _merge
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg PCCount oil_eur
    quietly capture estat sbsingle, breakvars(, const)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}

** pcresponse 
putexcel set "$P_Data_Processed/structural_breaks_pcresponse_diesel.xlsx", replace
putexcel A1=("StID")
putexcel B1=("BD")
putexcel C1=("P")

local row=2
local files: dir "$P_Data/break_pcresponse/Separate" files "*diesel.dta"
local n_stid: word count `files'
foreach f in `files' {
    use "$P_Data/break_pcresponse/Separate/`f'", clear
    quietly capture tsset y2kW
	quietly merge 1:1 y2kW using "$P_Data_Processed/weekly_oil.dta"
	quietly capture keep if _merge==3 
	quietly capture drop _merge
    quietly capture putexcel A`row'=("`f'")

    quietly capture reg Response PCCount oil_eur
    quietly capture estat sbsingle, breakvars(, const)
    quietly capture putexcel B`row'=(r(breakdate))
    quietly capture putexcel C`row'=(r(p))

    local row=`row'+1
    display `row'/`n_stid'
}
